/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package workforce.limousine.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;
import limousine.vo.Lookup;
import org.workforce.util.Utils;

/**
 *
 * @author krisada
 */
public class LookupService implements Service {

    public DataSource dataSource = null;
    private int limit = 50;

    public LookupService() {
        System.out.println("Initial lookup");
    }

    public int getLimit() {
        return limit;
    }

    public void setLimit(int limit) {
        this.limit = limit;
    }

    public boolean save(Object obj) {
        try {
            Connection connection = dataSource.getConnection();
            System.out.println("Save lookup");
            Lookup lookup = (Lookup) obj;
            int count = 0;
            StringBuilder sql = new StringBuilder();
            sql.append("INSERT INTO lookup(LOOKUP_ID, LOOKUP_NAME, ");
            sql.append("LOOKUP_DESCRIPTION, LOOKUP_GROUP_ID, LOOKUP_ORDER, LOOKUP_VALUE)");
            sql.append("VALUES(?,?,?,?,?,?)");
            PreparedStatement psmt = connection.prepareStatement(sql.toString());
            psmt.setString(1, lookup.getLookupId());
            psmt.setString(2, lookup.getLookupName());
            psmt.setString(3, lookup.getLookupDescription());
            psmt.setString(4, lookup.getLookupGroupId());
            psmt.setInt(5, lookup.getLookupOrder());
            psmt.setString(6, lookup.getLookupValue());
            count = psmt.executeUpdate();
            psmt.close();
            if (psmt != null) {
                psmt = null;
            }

            connection.close();
            if (connection != null) {
                connection = null;
            }
            return (count > 0) ? true : false;
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return false;
    }

    public boolean update(Object obj) {
        try {
            Connection connection = dataSource.getConnection();
            System.out.println("Update lookup");
            Lookup lookup = (Lookup) obj;
            int count = 0;
            StringBuilder sql = new StringBuilder();
            sql.append("UPDATE lookup SET LOOKUP_ID = ?, LOOKUP_NAME = ?, LOOKUP_DESCRIPTION =?,");
            sql.append("LOOKUP_GROUP_ID = ?, LOOKUP_ORDER = ?, LOOKUP_VALUE = ? WHERE TABLE_ID = ?");
            PreparedStatement psmt = connection.prepareStatement(sql.toString());
            psmt.setString(1, lookup.getLookupId());
            psmt.setString(2, lookup.getLookupName());
            psmt.setString(3, lookup.getLookupDescription());
            psmt.setString(4, lookup.getLookupGroupId());
            psmt.setInt(5, lookup.getLookupOrder());
            psmt.setString(6, lookup.getLookupValue());
            psmt.setInt(7, lookup.getTableId());
            count = psmt.executeUpdate();
            psmt.close();
            if (psmt != null) {
                psmt = null;
            }

            connection.close();
            if (connection != null) {
                connection = null;
            }
            return (count > 0) ? true : false;
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return false;
    }

    public boolean remove(Object obj) {
        try {
            Connection conn = dataSource.getConnection();
            System.out.println("remove lookup");
            StringBuilder s = new StringBuilder();
            s.append("DELETE FROM lookup WHERE TABLE_ID = ?");
            System.out.println("sql[" + s.toString() + "]");
            PreparedStatement pstm = conn.prepareStatement(s.toString());
            pstm.setString(1, (String) obj);
            int ret = pstm.executeUpdate();
            pstm.close();
            if (pstm != null) {
                pstm = null;
            }

            conn.close();
            if (conn != null) {
                conn = null;
            }
            return (ret == 1) ? true : false;
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return false;
    }

    public Object findById(String id) {
        try {
            Connection conn = dataSource.getConnection();
            System.out.println("FindById");
            StringBuilder s = new StringBuilder();
            s.append("SELECT ");
            s.append("IFNULL(A.TABLE_ID, '-') AS TABLE_ID,");
            s.append("IFNULL(A.LOOKUP_ID, '-') AS LOOKUP_ID,"); 
            s.append("IFNULL(A.LOOKUP_NAME, '-') AS LOOKUP_NAME,"); 
            s.append("IFNULL(A.LOOKUP_DESCRIPTION, '-') AS LOOKUP_DESCRIPTION,");
            s.append("IFNULL(A.LOOKUP_GROUP_ID, '-') AS LOOKUP_GROUP_ID,");
            s.append("IFNULL(A.LOOKUP_ORDER, 0) AS LOOKUP_ORDER,"); 
            s.append("IFNULL(A.LOOKUP_VALUE, '-') AS LOOKUP_VALUE,");
            s.append("now() AS LOOKUP_DATE ");
            s.append("FROM lookup A WHERE A.TABLE_ID = ?");
            System.out.println("sql[" + s.toString() + "]");
            PreparedStatement pstm = conn.prepareStatement(s.toString());
            pstm.setInt(1, Integer.parseInt(id));
            ResultSet rs = pstm.executeQuery();
            Lookup lookup = new Lookup();
            if (rs.next()) {
                System.out.println("-------- Object found ---------");
                lookup.setTableId(rs.getInt(1));
                lookup.setLookupId(Utils.StringField(rs.getString(2)));
                lookup.setLookupName(Utils.StringField(rs.getString(3)));
                lookup.setLookupDescription(Utils.StringField(rs.getString(4)));
                lookup.setLookupGroupId(Utils.StringField(rs.getString(5)));
                lookup.setLookupOrder(rs.getInt(6));
                lookup.setLookupValue(Utils.StringField(rs.getString(7)));
            }
            rs.close();
            if (rs != null) {
                rs = null;
            }

            pstm.close();
            if (pstm != null) {
                pstm = null;
            }

            conn.close();
            if (conn != null) {
                conn = null;
            }
            return lookup;
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return null;
    }

    public List find(Object[] obj) {
        List<Lookup> list = new ArrayList<Lookup>();
        try {
            Connection connection = dataSource.getConnection();
            System.out.println("Save lookup");
            connection.close();
            return list;
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return list;
    }

    public List<Lookup> searchGroup(String groupId) {
        List<Lookup> list = new ArrayList<Lookup>();
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            StringBuilder s = new StringBuilder();
            s.append("SELECT ");
            s.append("IFNULL(A.TABLE_ID, '-') AS TABLE_ID,");
            s.append("IFNULL(A.LOOKUP_ID, '-') AS LOOKUP_ID,"); 
            s.append("IFNULL(A.LOOKUP_NAME, '-') AS LOOKUP_NAME,"); 
            s.append("IFNULL(A.LOOKUP_DESCRIPTION, '-') AS LOOKUP_DESCRIPTION,");
            s.append("IFNULL(A.LOOKUP_GROUP_ID, '-') AS LOOKUP_GROUP_ID,");
            s.append("IFNULL(A.LOOKUP_ORDER, 0) AS LOOKUP_ORDER,"); 
            s.append("IFNULL(A.LOOKUP_VALUE, '-') AS LOOKUP_VALUE ");
            s.append("FROM lookup A WHERE A.GROUP_ID = ?");
            System.out.println("sql[" + s.toString() + "]");
            PreparedStatement stmt = conn.prepareStatement(s.toString());
            stmt.setString(1, groupId);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                Lookup lookup = new Lookup();
                lookup.setTableId(rs.getInt(1));
                lookup.setLookupId(Utils.StringField(rs.getString(2)));
                lookup.setLookupName(Utils.StringField(rs.getString(3)));
                lookup.setLookupDescription(Utils.StringField(rs.getString(4)));
                lookup.setLookupGroupId(Utils.StringField(rs.getString(5)));
                lookup.setLookupOrder(rs.getInt(6));
                lookup.setLookupValue(rs.getString(7));
                list.add(lookup);
            }
            rs.close();
            rs = null;
            stmt.close();
            stmt = null;
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                    conn = null;
                } catch (Exception ex) {
                }
            }
        }
        return list;
    }

    public List<Lookup> search(int offset, Object[] certiria) {
        List<Lookup> list = new ArrayList<Lookup>();
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            StringBuilder s = new StringBuilder();
            s.append("SELECT ");
            s.append("IFNULL(A.TABLE_ID, '-') AS TABLE_ID,");
            s.append("IFNULL(A.LOOKUP_ID, '-') AS LOOKUP_ID,"); 
            s.append("IFNULL(A.LOOKUP_NAME, '-') AS LOOKUP_NAME,"); 
            s.append("IFNULL(A.LOOKUP_DESCRIPTION, '-') AS LOOKUP_DESCRIPTION,");
            s.append("IFNULL(A.LOOKUP_GROUP_ID, '-') AS LOOKUP_GROUP_ID,");
            s.append("IFNULL(A.LOOKUP_ORDER, 0) AS LOOKUP_ORDER,"); 
            s.append("IFNULL(A.LOOKUP_VALUE, '-') AS LOOKUP_VALUE,");
            s.append("now() AS LOOKUP_DATE ");
            s.append("FROM lookup A WHERE A.LOOKUP_NAME LIKE ? AND A.LOOKUP_GROUP_ID LIKE ?");
            s.append(" LIMIT ? OFFSET ?");
            System.out.println("sql[" + s.toString() + "]");
            PreparedStatement stmt = conn.prepareStatement(s.toString());
            String tokens = ((String) certiria[0]).replaceAll("_comma_", ",");
            String name = tokens.split(",")[0];
            String group = tokens.split(",")[1];
            System.out.println("name["+ name +"]["+ group+"]");
            name = (name != null) ? name.replace('*', '%') : "";
            group = (group != null) ? group.replace('*', '%') : "";
            stmt.setString(1, name);
            stmt.setString(2, group);
            stmt.setInt(3, limit);
            stmt.setInt(4, offset);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                Lookup lookup = new Lookup();
                lookup.setTableId(rs.getInt(1));
                lookup.setLookupId(Utils.StringField(rs.getString(2)));
                lookup.setLookupName(Utils.StringField(rs.getString(3)));
                lookup.setLookupDescription(Utils.StringField(rs.getString(4)));
                lookup.setLookupGroupId(Utils.StringField(rs.getString(5)));
                lookup.setLookupOrder(rs.getInt(6));
                lookup.setLookupValue(rs.getString(7));
                list.add(lookup);
            }
            rs.close();
            rs = null;
            stmt.close();
            stmt = null;
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                    conn = null;
                } catch (Exception ex) {
                }
            }
        }
        return list;
    }

    public int searchCount(int offset, Object[] certiria) {
        int count = 0;
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            StringBuilder s = new StringBuilder();
            s.append("SELECT COUNT(*) FROM lookup WHERE LOOKUP_NAME LIKE ? AND LOOKUP_GROUP_ID LIKE ?");
            System.out.println("sql[" + s.toString() + "]");
            PreparedStatement stmt = conn.prepareStatement(s.toString());
            String tokens = ((String) certiria[0]).replaceAll("_comma_", ",");
            String name = tokens.split(",")[0];
            String group = tokens.split(",")[1];
            name = (name != null) ? name.replace('*', '%') : "";
            group = (group != null) ? group.replace('*', '%') : "";
            stmt.setString(1, name);
            stmt.setString(2, group);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                count = rs.getInt(1);
            }
            rs.close();
            rs = null;
            stmt.close();
            stmt = null;
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                    conn = null;
                } catch (Exception ex) {
                }
            }
        }
        return count;
    }

    public boolean removeAll(String[] ids) {
        try {
            Connection conn = dataSource.getConnection();
            System.out.println("Save lookup");
            StringBuilder s = new StringBuilder();
            s.append("DELETE FROM lookup WHERE TABLE_ID = ?");
            System.out.println("sql[" + s.toString() + "]");
            Statement pstm = conn.createStatement();
            for (int i = 0; i < ids.length; i++) {
                pstm.addBatch("DELETE FROM lookup WHERE TABLE_ID = '" + ids[i] + "'");
            }
            int ret[] = pstm.executeBatch();
            pstm.close();
            if (pstm != null) {
                pstm = null;
            }
            conn.close();
            if (conn != null) {
                conn = null;
            }
            return (ret.length == ids.length) ? true : false;
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return false;
    }

    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public Object findById(String uid, String id) {
        throw new UnsupportedOperationException("Not supported yet.");
    }
    
    
}